<!doctype html>
<html>
<head>
<meta charset='UTF-8'><meta name='viewport' content='width=device-width initial-scale=1'>

<link href='https://fonts.loli.net/css?family=Open+Sans:400italic,700italic,700,400&subset=latin,latin-ext' rel='stylesheet' type='text/css' /><style type='text/css'>html {overflow-x: initial !important;}:root { --bg-color:#ffffff; --text-color:#333333; --select-text-bg-color:#B5D6FC; --select-text-font-color:auto; --monospace:"Lucida Console",Consolas,"Courier",monospace; --title-bar-height:20px; }
.mac-os-11 { --title-bar-height:28px; }
html { font-size: 14px; background-color: var(--bg-color); color: var(--text-color); font-family: "Helvetica Neue", Helvetica, Arial, sans-serif; -webkit-font-smoothing: antialiased; }
body { margin: 0px; padding: 0px; height: auto; bottom: 0px; top: 0px; left: 0px; right: 0px; font-size: 1rem; line-height: 1.42857; overflow-x: hidden; background: inherit; tab-size: 4; }
iframe { margin: auto; }
a.url { word-break: break-all; }
a:active, a:hover { outline: 0px; }
.in-text-selection, ::selection { text-shadow: none; background: var(--select-text-bg-color); color: var(--select-text-font-color); }
#write { margin: 0px auto; height: auto; width: inherit; word-break: normal; overflow-wrap: break-word; position: relative; white-space: normal; overflow-x: visible; padding-top: 36px; }
#write.first-line-indent p { text-indent: 2em; }
#write.first-line-indent li p, #write.first-line-indent p * { text-indent: 0px; }
#write.first-line-indent li { margin-left: 2em; }
.for-image #write { padding-left: 8px; padding-right: 8px; }
body.typora-export { padding-left: 30px; padding-right: 30px; }
.typora-export .footnote-line, .typora-export li, .typora-export p { white-space: pre-wrap; }
.typora-export .task-list-item input { pointer-events: none; }
@media screen and (max-width: 500px) {
  body.typora-export { padding-left: 0px; padding-right: 0px; }
  #write { padding-left: 20px; padding-right: 20px; }
  .CodeMirror-sizer { margin-left: 0px !important; }
  .CodeMirror-gutters { display: none !important; }
}
#write li > figure:last-child { margin-bottom: 0.5rem; }
#write ol, #write ul { position: relative; }
img { max-width: 100%; vertical-align: middle; image-orientation: from-image; }
button, input, select, textarea { color: inherit; font: inherit; }
input[type="checkbox"], input[type="radio"] { line-height: normal; padding: 0px; }
*, ::after, ::before { box-sizing: border-box; }
#write h1, #write h2, #write h3, #write h4, #write h5, #write h6, #write p, #write pre { width: inherit; }
#write h1, #write h2, #write h3, #write h4, #write h5, #write h6, #write p { position: relative; }
p { line-height: inherit; }
h1, h2, h3, h4, h5, h6 { break-after: avoid-page; break-inside: avoid; orphans: 4; }
p { orphans: 4; }
h1 { font-size: 2rem; }
h2 { font-size: 1.8rem; }
h3 { font-size: 1.6rem; }
h4 { font-size: 1.4rem; }
h5 { font-size: 1.2rem; }
h6 { font-size: 1rem; }
.md-math-block, .md-rawblock, h1, h2, h3, h4, h5, h6, p { margin-top: 1rem; margin-bottom: 1rem; }
.hidden { display: none; }
.md-blockmeta { color: rgb(204, 204, 204); font-weight: 700; font-style: italic; }
a { cursor: pointer; }
sup.md-footnote { padding: 2px 4px; background-color: rgba(238, 238, 238, 0.7); color: rgb(85, 85, 85); border-radius: 4px; cursor: pointer; }
sup.md-footnote a, sup.md-footnote a:hover { color: inherit; text-transform: inherit; text-decoration: inherit; }
#write input[type="checkbox"] { cursor: pointer; width: inherit; height: inherit; }
figure { overflow-x: auto; margin: 1.2em 0px; max-width: calc(100% + 16px); padding: 0px; }
figure > table { margin: 0px; }
tr { break-inside: avoid; break-after: auto; }
thead { display: table-header-group; }
table { border-collapse: collapse; border-spacing: 0px; width: 100%; overflow: auto; break-inside: auto; text-align: left; }
table.md-table td { min-width: 32px; }
.CodeMirror-gutters { border-right: 0px; background-color: inherit; }
.CodeMirror-linenumber { user-select: none; }
.CodeMirror { text-align: left; }
.CodeMirror-placeholder { opacity: 0.3; }
.CodeMirror pre { padding: 0px 4px; }
.CodeMirror-lines { padding: 0px; }
div.hr:focus { cursor: none; }
#write pre { white-space: pre-wrap; }
#write.fences-no-line-wrapping pre { white-space: pre; }
#write pre.ty-contain-cm { white-space: normal; }
.CodeMirror-gutters { margin-right: 4px; }
.md-fences { font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; overflow: visible; white-space: pre; background: inherit; position: relative !important; }
.md-fences-adv-panel { width: 100%; margin-top: 10px; text-align: center; padding-top: 0px; padding-bottom: 8px; overflow-x: auto; }
#write .md-fences.mock-cm { white-space: pre-wrap; }
.md-fences.md-fences-with-lineno { padding-left: 0px; }
#write.fences-no-line-wrapping .md-fences.mock-cm { white-space: pre; overflow-x: auto; }
.md-fences.mock-cm.md-fences-with-lineno { padding-left: 8px; }
.CodeMirror-line, twitterwidget { break-inside: avoid; }
.footnotes { opacity: 0.8; font-size: 0.9rem; margin-top: 1em; margin-bottom: 1em; }
.footnotes + .footnotes { margin-top: 0px; }
.md-reset { margin: 0px; padding: 0px; border: 0px; outline: 0px; vertical-align: top; background: 0px 0px; text-decoration: none; text-shadow: none; float: none; position: static; width: auto; height: auto; white-space: nowrap; cursor: inherit; -webkit-tap-highlight-color: transparent; line-height: normal; font-weight: 400; text-align: left; box-sizing: content-box; direction: ltr; }
li div { padding-top: 0px; }
blockquote { margin: 1rem 0px; }
li .mathjax-block, li p { margin: 0.5rem 0px; }
li blockquote { margin: 1rem 0px; }
li { margin: 0px; position: relative; }
blockquote > :last-child { margin-bottom: 0px; }
blockquote > :first-child, li > :first-child { margin-top: 0px; }
.footnotes-area { color: rgb(136, 136, 136); margin-top: 0.714rem; padding-bottom: 0.143rem; white-space: normal; }
#write .footnote-line { white-space: pre-wrap; }
@media print {
  body, html { border: 1px solid transparent; height: 99%; break-after: avoid; break-before: avoid; font-variant-ligatures: no-common-ligatures; }
  #write { margin-top: 0px; padding-top: 0px; border-color: transparent !important; }
  .typora-export * { -webkit-print-color-adjust: exact; }
  .typora-export #write { break-after: avoid; }
  .typora-export #write::after { height: 0px; }
  .is-mac table { break-inside: avoid; }
  .typora-export-show-outline .typora-export-sidebar { display: none; }
}
.footnote-line { margin-top: 0.714em; font-size: 0.7em; }
a img, img a { cursor: pointer; }
pre.md-meta-block { font-size: 0.8rem; min-height: 0.8rem; white-space: pre-wrap; background: rgb(204, 204, 204); display: block; overflow-x: hidden; }
p > .md-image:only-child:not(.md-img-error) img, p > img:only-child { display: block; margin: auto; }
#write.first-line-indent p > .md-image:only-child:not(.md-img-error) img { left: -2em; position: relative; }
p > .md-image:only-child { display: inline-block; width: 100%; }
#write .MathJax_Display { margin: 0.8em 0px 0px; }
.md-math-block { width: 100%; }
.md-math-block:not(:empty)::after { display: none; }
.MathJax_ref { fill: currentcolor; }
[contenteditable="true"]:active, [contenteditable="true"]:focus, [contenteditable="false"]:active, [contenteditable="false"]:focus { outline: 0px; box-shadow: none; }
.md-task-list-item { position: relative; list-style-type: none; }
.task-list-item.md-task-list-item { padding-left: 0px; }
.md-task-list-item > input { position: absolute; top: 0px; left: 0px; margin-left: -1.2em; margin-top: calc(1em - 10px); border: none; }
.math { font-size: 1rem; }
.md-toc { min-height: 3.58rem; position: relative; font-size: 0.9rem; border-radius: 10px; }
.md-toc-content { position: relative; margin-left: 0px; }
.md-toc-content::after, .md-toc::after { display: none; }
.md-toc-item { display: block; color: rgb(65, 131, 196); }
.md-toc-item a { text-decoration: none; }
.md-toc-inner:hover { text-decoration: underline; }
.md-toc-inner { display: inline-block; cursor: pointer; }
.md-toc-h1 .md-toc-inner { margin-left: 0px; font-weight: 700; }
.md-toc-h2 .md-toc-inner { margin-left: 2em; }
.md-toc-h3 .md-toc-inner { margin-left: 4em; }
.md-toc-h4 .md-toc-inner { margin-left: 6em; }
.md-toc-h5 .md-toc-inner { margin-left: 8em; }
.md-toc-h6 .md-toc-inner { margin-left: 10em; }
@media screen and (max-width: 48em) {
  .md-toc-h3 .md-toc-inner { margin-left: 3.5em; }
  .md-toc-h4 .md-toc-inner { margin-left: 5em; }
  .md-toc-h5 .md-toc-inner { margin-left: 6.5em; }
  .md-toc-h6 .md-toc-inner { margin-left: 8em; }
}
a.md-toc-inner { font-size: inherit; font-style: inherit; font-weight: inherit; line-height: inherit; }
.footnote-line a:not(.reversefootnote) { color: inherit; }
.md-attr { display: none; }
.md-fn-count::after { content: "."; }
code, pre, samp, tt { font-family: var(--monospace); }
kbd { margin: 0px 0.1em; padding: 0.1em 0.6em; font-size: 0.8em; color: rgb(36, 39, 41); background: rgb(255, 255, 255); border: 1px solid rgb(173, 179, 185); border-radius: 3px; box-shadow: rgba(12, 13, 14, 0.2) 0px 1px 0px, rgb(255, 255, 255) 0px 0px 0px 2px inset; white-space: nowrap; vertical-align: middle; }
.md-comment { color: rgb(162, 127, 3); opacity: 0.8; font-family: var(--monospace); }
code { text-align: left; vertical-align: initial; }
a.md-print-anchor { white-space: pre !important; border-width: initial !important; border-style: none !important; border-color: initial !important; display: inline-block !important; position: absolute !important; width: 1px !important; right: 0px !important; outline: 0px !important; background: 0px 0px !important; text-decoration: initial !important; text-shadow: initial !important; }
.md-inline-math .MathJax_SVG .noError { display: none !important; }
.html-for-mac .inline-math-svg .MathJax_SVG { vertical-align: 0.2px; }
.md-fences-math .MathJax_SVG_Display, .md-math-block .MathJax_SVG_Display { text-align: center; margin: 0px; position: relative; text-indent: 0px; max-width: none; max-height: none; min-height: 0px; min-width: 100%; width: auto; overflow-y: visible; display: block !important; }
.MathJax_SVG_Display, .md-inline-math .MathJax_SVG_Display { width: auto; margin: inherit; display: inline-block !important; }
.MathJax_SVG .MJX-monospace { font-family: var(--monospace); }
.MathJax_SVG .MJX-sans-serif { font-family: sans-serif; }
.MathJax_SVG { display: inline; font-style: normal; font-weight: 400; line-height: normal; text-indent: 0px; text-align: left; text-transform: none; letter-spacing: normal; word-spacing: normal; overflow-wrap: normal; white-space: nowrap; float: none; direction: ltr; max-width: none; max-height: none; min-width: 0px; min-height: 0px; border: 0px; padding: 0px; margin: 0px; zoom: 90%; }
#math-inline-preview-content { zoom: 1.1; }
.MathJax_SVG * { transition: none 0s ease 0s; }
.MathJax_SVG_Display svg { vertical-align: middle !important; margin-bottom: 0px !important; margin-top: 0px !important; }
.os-windows.monocolor-emoji .md-emoji { font-family: "Segoe UI Symbol", sans-serif; }
.md-diagram-panel > svg { max-width: 100%; }
[lang="flow"] svg, [lang="mermaid"] svg { max-width: 100%; height: auto; }
[lang="mermaid"] .node text { font-size: 1rem; }
table tr th { border-bottom: 0px; }
video { max-width: 100%; display: block; margin: 0px auto; }
iframe { max-width: 100%; width: 100%; border: none; }
.highlight td, .highlight tr { border: 0px; }
mark { background: rgb(255, 255, 0); color: rgb(0, 0, 0); }
.md-html-inline .md-plain, .md-html-inline strong, mark .md-inline-math, mark strong { color: inherit; }
.md-expand mark .md-meta { opacity: 0.3 !important; }
mark .md-meta { color: rgb(0, 0, 0); }
@media print {
  .typora-export h1, .typora-export h2, .typora-export h3, .typora-export h4, .typora-export h5, .typora-export h6 { break-inside: avoid; }
}
.md-diagram-panel .messageText { stroke: none !important; }
.md-diagram-panel .start-state { fill: var(--node-fill); }
.md-diagram-panel .edgeLabel rect { opacity: 1 !important; }
.md-require-zoom-fix foreignobject { font-size: var(--mermaid-font-zoom); }
.md-fences.md-fences-math { font-size: 1em; }
.md-fences-math .MathJax_SVG_Display { margin-top: 8px; cursor: default; }
.md-fences-advanced:not(.md-focus) { padding: 0px; white-space: nowrap; border: 0px; }
.md-fences-advanced:not(.md-focus) { background: inherit; }
.typora-export-show-outline .typora-export-content { max-width: 1440px; margin: auto; display: flex; flex-direction: row; }
.typora-export-sidebar { width: 300px; font-size: 0.8rem; margin-top: 80px; margin-right: 18px; }
.typora-export-show-outline #write { --webkit-flex:2; flex: 2 1 0%; }
.typora-export-sidebar .outline-content { position: fixed; top: 0px; max-height: 100%; overflow: hidden auto; padding-bottom: 30px; padding-top: 60px; width: 300px; }
@media screen and (max-width: 1024px) {
  .typora-export-sidebar, .typora-export-sidebar .outline-content { width: 240px; }
}
@media screen and (max-width: 800px) {
  .typora-export-sidebar { display: none; }
}
.outline-content li, .outline-content ul { margin-left: 0px; margin-right: 0px; padding-left: 0px; padding-right: 0px; list-style: none; }
.outline-content ul { margin-top: 0px; margin-bottom: 0px; }
.outline-content strong { font-weight: 400; }
.outline-expander { width: 1rem; height: 1.42857rem; position: relative; display: table-cell; vertical-align: middle; cursor: pointer; padding-left: 4px; }
.outline-expander::before { content: ""; position: relative; font-family: Ionicons; display: inline-block; font-size: 8px; vertical-align: middle; }
.outline-item { padding-top: 3px; padding-bottom: 3px; cursor: pointer; }
.outline-expander:hover::before { content: ""; }
.outline-h1 > .outline-item { padding-left: 0px; }
.outline-h2 > .outline-item { padding-left: 1em; }
.outline-h3 > .outline-item { padding-left: 2em; }
.outline-h4 > .outline-item { padding-left: 3em; }
.outline-h5 > .outline-item { padding-left: 4em; }
.outline-h6 > .outline-item { padding-left: 5em; }
.outline-label { cursor: pointer; display: table-cell; vertical-align: middle; text-decoration: none; color: inherit; }
.outline-label:hover { text-decoration: underline; }
.outline-item:hover { border-color: rgb(245, 245, 245); background-color: var(--item-hover-bg-color); }
.outline-item:hover { margin-left: -28px; margin-right: -28px; border-left: 28px solid transparent; border-right: 28px solid transparent; }
.outline-item-single .outline-expander::before, .outline-item-single .outline-expander:hover::before { display: none; }
.outline-item-open > .outline-item > .outline-expander::before { content: ""; }
.outline-children { display: none; }
.info-panel-tab-wrapper { display: none; }
.outline-item-open > .outline-children { display: block; }
.typora-export .outline-item { padding-top: 1px; padding-bottom: 1px; }
.typora-export .outline-item:hover { margin-right: -8px; border-right: 8px solid transparent; }
.typora-export .outline-expander::before { content: "+"; font-family: inherit; top: -1px; }
.typora-export .outline-expander:hover::before, .typora-export .outline-item-open > .outline-item > .outline-expander::before { content: "−"; }
.typora-export-collapse-outline .outline-children { display: none; }
.typora-export-collapse-outline .outline-item-open > .outline-children, .typora-export-no-collapse-outline .outline-children { display: block; }
.typora-export-no-collapse-outline .outline-expander::before { content: "" !important; }
.typora-export-show-outline .outline-item-active > .outline-item .outline-label { font-weight: 700; }


.CodeMirror { height: auto; }
.CodeMirror.cm-s-inner { background: inherit; }
.CodeMirror-scroll { overflow: auto hidden; z-index: 3; }
.CodeMirror-gutter-filler, .CodeMirror-scrollbar-filler { background-color: rgb(255, 255, 255); }
.CodeMirror-gutters { border-right: 1px solid rgb(221, 221, 221); background: inherit; white-space: nowrap; }
.CodeMirror-linenumber { padding: 0px 3px 0px 5px; text-align: right; color: rgb(153, 153, 153); }
.cm-s-inner .cm-keyword { color: rgb(119, 0, 136); }
.cm-s-inner .cm-atom, .cm-s-inner.cm-atom { color: rgb(34, 17, 153); }
.cm-s-inner .cm-number { color: rgb(17, 102, 68); }
.cm-s-inner .cm-def { color: rgb(0, 0, 255); }
.cm-s-inner .cm-variable { color: rgb(0, 0, 0); }
.cm-s-inner .cm-variable-2 { color: rgb(0, 85, 170); }
.cm-s-inner .cm-variable-3 { color: rgb(0, 136, 85); }
.cm-s-inner .cm-string { color: rgb(170, 17, 17); }
.cm-s-inner .cm-property { color: rgb(0, 0, 0); }
.cm-s-inner .cm-operator { color: rgb(152, 26, 26); }
.cm-s-inner .cm-comment, .cm-s-inner.cm-comment { color: rgb(170, 85, 0); }
.cm-s-inner .cm-string-2 { color: rgb(255, 85, 0); }
.cm-s-inner .cm-meta { color: rgb(85, 85, 85); }
.cm-s-inner .cm-qualifier { color: rgb(85, 85, 85); }
.cm-s-inner .cm-builtin { color: rgb(51, 0, 170); }
.cm-s-inner .cm-bracket { color: rgb(153, 153, 119); }
.cm-s-inner .cm-tag { color: rgb(17, 119, 0); }
.cm-s-inner .cm-attribute { color: rgb(0, 0, 204); }
.cm-s-inner .cm-header, .cm-s-inner.cm-header { color: rgb(0, 0, 255); }
.cm-s-inner .cm-quote, .cm-s-inner.cm-quote { color: rgb(0, 153, 0); }
.cm-s-inner .cm-hr, .cm-s-inner.cm-hr { color: rgb(153, 153, 153); }
.cm-s-inner .cm-link, .cm-s-inner.cm-link { color: rgb(0, 0, 204); }
.cm-negative { color: rgb(221, 68, 68); }
.cm-positive { color: rgb(34, 153, 34); }
.cm-header, .cm-strong { font-weight: 700; }
.cm-del { text-decoration: line-through; }
.cm-em { font-style: italic; }
.cm-link { text-decoration: underline; }
.cm-error { color: red; }
.cm-invalidchar { color: red; }
.cm-constant { color: rgb(38, 139, 210); }
.cm-defined { color: rgb(181, 137, 0); }
div.CodeMirror span.CodeMirror-matchingbracket { color: rgb(0, 255, 0); }
div.CodeMirror span.CodeMirror-nonmatchingbracket { color: rgb(255, 34, 34); }
.cm-s-inner .CodeMirror-activeline-background { background: inherit; }
.CodeMirror { position: relative; overflow: hidden; }
.CodeMirror-scroll { height: 100%; outline: 0px; position: relative; box-sizing: content-box; background: inherit; }
.CodeMirror-sizer { position: relative; }
.CodeMirror-gutter-filler, .CodeMirror-hscrollbar, .CodeMirror-scrollbar-filler, .CodeMirror-vscrollbar { position: absolute; z-index: 6; display: none; }
.CodeMirror-vscrollbar { right: 0px; top: 0px; overflow: hidden; }
.CodeMirror-hscrollbar { bottom: 0px; left: 0px; overflow: hidden; }
.CodeMirror-scrollbar-filler { right: 0px; bottom: 0px; }
.CodeMirror-gutter-filler { left: 0px; bottom: 0px; }
.CodeMirror-gutters { position: absolute; left: 0px; top: 0px; padding-bottom: 30px; z-index: 3; }
.CodeMirror-gutter { white-space: normal; height: 100%; box-sizing: content-box; padding-bottom: 30px; margin-bottom: -32px; display: inline-block; }
.CodeMirror-gutter-wrapper { position: absolute; z-index: 4; background: 0px 0px !important; border: none !important; }
.CodeMirror-gutter-background { position: absolute; top: 0px; bottom: 0px; z-index: 4; }
.CodeMirror-gutter-elt { position: absolute; cursor: default; z-index: 4; }
.CodeMirror-lines { cursor: text; }
.CodeMirror pre { border-radius: 0px; border-width: 0px; background: 0px 0px; font-family: inherit; font-size: inherit; margin: 0px; white-space: pre; overflow-wrap: normal; color: inherit; z-index: 2; position: relative; overflow: visible; }
.CodeMirror-wrap pre { overflow-wrap: break-word; white-space: pre-wrap; word-break: normal; }
.CodeMirror-code pre { border-right: 30px solid transparent; width: fit-content; }
.CodeMirror-wrap .CodeMirror-code pre { border-right: none; width: auto; }
.CodeMirror-linebackground { position: absolute; left: 0px; right: 0px; top: 0px; bottom: 0px; z-index: 0; }
.CodeMirror-linewidget { position: relative; z-index: 2; overflow: auto; }
.CodeMirror-wrap .CodeMirror-scroll { overflow-x: hidden; }
.CodeMirror-measure { position: absolute; width: 100%; height: 0px; overflow: hidden; visibility: hidden; }
.CodeMirror-measure pre { position: static; }
.CodeMirror div.CodeMirror-cursor { position: absolute; visibility: hidden; border-right: none; width: 0px; }
.CodeMirror div.CodeMirror-cursor { visibility: hidden; }
.CodeMirror-focused div.CodeMirror-cursor { visibility: inherit; }
.cm-searching { background: rgba(255, 255, 0, 0.4); }
span.cm-underlined { text-decoration: underline; }
span.cm-strikethrough { text-decoration: line-through; }
.cm-tw-syntaxerror { color: rgb(255, 255, 255); background-color: rgb(153, 0, 0); }
.cm-tw-deleted { text-decoration: line-through; }
.cm-tw-header5 { font-weight: 700; }
.cm-tw-listitem:first-child { padding-left: 10px; }
.cm-tw-box { border-style: solid; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-color: inherit; border-top-width: 0px !important; }
.cm-tw-underline { text-decoration: underline; }
@media print {
  .CodeMirror div.CodeMirror-cursor { visibility: hidden; }
}


:root {
    --side-bar-bg-color: #fafafa;
    --control-text-color: #777;
}

@include-when-export url(https://fonts.loli.net/css?family=Open+Sans:400italic,700italic,700,400&subset=latin,latin-ext);

/* open-sans-regular - latin-ext_latin */
  /* open-sans-italic - latin-ext_latin */
    /* open-sans-700 - latin-ext_latin */
    /* open-sans-700italic - latin-ext_latin */
  html {
    font-size: 16px;
}

body {
    font-family: "Open Sans","Clear Sans", "Helvetica Neue", Helvetica, Arial, sans-serif;
    color: rgb(51, 51, 51);
    line-height: 1.6;
}

#write {
    max-width: 860px;
  	margin: 0 auto;
  	padding: 30px;
    padding-bottom: 100px;
}

@media only screen and (min-width: 1400px) {
	#write {
		max-width: 1024px;
	}
}

@media only screen and (min-width: 1800px) {
	#write {
		max-width: 1200px;
	}
}

#write > ul:first-child,
#write > ol:first-child{
    margin-top: 30px;
}

a {
    color: #4183C4;
}
h1,
h2,
h3,
h4,
h5,
h6 {
    position: relative;
    margin-top: 1rem;
    margin-bottom: 1rem;
    font-weight: bold;
    line-height: 1.4;
    cursor: text;
}
h1:hover a.anchor,
h2:hover a.anchor,
h3:hover a.anchor,
h4:hover a.anchor,
h5:hover a.anchor,
h6:hover a.anchor {
    text-decoration: none;
}
h1 tt,
h1 code {
    font-size: inherit;
}
h2 tt,
h2 code {
    font-size: inherit;
}
h3 tt,
h3 code {
    font-size: inherit;
}
h4 tt,
h4 code {
    font-size: inherit;
}
h5 tt,
h5 code {
    font-size: inherit;
}
h6 tt,
h6 code {
    font-size: inherit;
}
h1 {
    font-size: 2.25em;
    line-height: 1.2;
    border-bottom: 1px solid #eee;
}
h2 {
    font-size: 1.75em;
    line-height: 1.225;
    border-bottom: 1px solid #eee;
}

/*@media print {
    .typora-export h1,
    .typora-export h2 {
        border-bottom: none;
        padding-bottom: initial;
    }

    .typora-export h1::after,
    .typora-export h2::after {
        content: "";
        display: block;
        height: 100px;
        margin-top: -96px;
        border-top: 1px solid #eee;
    }
}*/

h3 {
    font-size: 1.5em;
    line-height: 1.43;
}
h4 {
    font-size: 1.25em;
}
h5 {
    font-size: 1em;
}
h6 {
   font-size: 1em;
    color: #777;
}
p,
blockquote,
ul,
ol,
dl,
table{
    margin: 0.8em 0;
}
li>ol,
li>ul {
    margin: 0 0;
}
hr {
    height: 2px;
    padding: 0;
    margin: 16px 0;
    background-color: #e7e7e7;
    border: 0 none;
    overflow: hidden;
    box-sizing: content-box;
}

li p.first {
    display: inline-block;
}
ul,
ol {
    padding-left: 30px;
}
ul:first-child,
ol:first-child {
    margin-top: 0;
}
ul:last-child,
ol:last-child {
    margin-bottom: 0;
}
blockquote {
    border-left: 4px solid #dfe2e5;
    padding: 0 15px;
    color: #777777;
}
blockquote blockquote {
    padding-right: 0;
}
table {
    padding: 0;
    word-break: initial;
}
table tr {
    border: 1px solid #dfe2e5;
    margin: 0;
    padding: 0;
}
table tr:nth-child(2n),
thead {
    background-color: #f8f8f8;
}
table th {
    font-weight: bold;
    border: 1px solid #dfe2e5;
    border-bottom: 0;
    margin: 0;
    padding: 6px 13px;
}
table td {
    border: 1px solid #dfe2e5;
    margin: 0;
    padding: 6px 13px;
}
table th:first-child,
table td:first-child {
    margin-top: 0;
}
table th:last-child,
table td:last-child {
    margin-bottom: 0;
}

.CodeMirror-lines {
    padding-left: 4px;
}

.code-tooltip {
    box-shadow: 0 1px 1px 0 rgba(0,28,36,.3);
    border-top: 1px solid #eef2f2;
}

.md-fences,
code,
tt {
    border: 1px solid #e7eaed;
    background-color: #f8f8f8;
    border-radius: 3px;
    padding: 0;
    padding: 2px 4px 0px 4px;
    font-size: 0.9em;
}

code {
    background-color: #f3f4f4;
    padding: 0 2px 0 2px;
}

.md-fences {
    margin-bottom: 15px;
    margin-top: 15px;
    padding-top: 8px;
    padding-bottom: 6px;
}


.md-task-list-item > input {
  margin-left: -1.3em;
}

@media print {
    html {
        font-size: 13px;
    }
    table,
    pre {
        page-break-inside: avoid;
    }
    pre {
        word-wrap: break-word;
    }
}

.md-fences {
	background-color: #f8f8f8;
}
#write pre.md-meta-block {
	padding: 1rem;
    font-size: 85%;
    line-height: 1.45;
    background-color: #f7f7f7;
    border: 0;
    border-radius: 3px;
    color: #777777;
    margin-top: 0 !important;
}

.mathjax-block>.code-tooltip {
	bottom: .375rem;
}

.md-mathjax-midline {
    background: #fafafa;
}

#write>h3.md-focus:before{
	left: -1.5625rem;
	top: .375rem;
}
#write>h4.md-focus:before{
	left: -1.5625rem;
	top: .285714286rem;
}
#write>h5.md-focus:before{
	left: -1.5625rem;
	top: .285714286rem;
}
#write>h6.md-focus:before{
	left: -1.5625rem;
	top: .285714286rem;
}
.md-image>.md-meta {
    /*border: 1px solid #ddd;*/
    border-radius: 3px;
    padding: 2px 0px 0px 4px;
    font-size: 0.9em;
    color: inherit;
}

.md-tag {
    color: #a7a7a7;
    opacity: 1;
}

.md-toc { 
    margin-top:20px;
    padding-bottom:20px;
}

.sidebar-tabs {
    border-bottom: none;
}

#typora-quick-open {
    border: 1px solid #ddd;
    background-color: #f8f8f8;
}

#typora-quick-open-item {
    background-color: #FAFAFA;
    border-color: #FEFEFE #e5e5e5 #e5e5e5 #eee;
    border-style: solid;
    border-width: 1px;
}

/** focus mode */
.on-focus-mode blockquote {
    border-left-color: rgba(85, 85, 85, 0.12);
}

header, .context-menu, .megamenu-content, footer{
    font-family: "Segoe UI", "Arial", sans-serif;
}

.file-node-content:hover .file-node-icon,
.file-node-content:hover .file-node-open-state{
    visibility: visible;
}

.mac-seamless-mode #typora-sidebar {
    background-color: #fafafa;
    background-color: var(--side-bar-bg-color);
}

.md-lang {
    color: #b4654d;
}

/*.html-for-mac {
    --item-hover-bg-color: #E6F0FE;
}*/

#md-notification .btn {
    border: 0;
}

.dropdown-menu .divider {
    border-color: #e5e5e5;
    opacity: 0.4;
}

.ty-preferences .window-content {
    background-color: #fafafa;
}

.ty-preferences .nav-group-item.active {
    color: white;
    background: #999;
}

.menu-item-container a.menu-style-btn {
    background-color: #f5f8fa;
    background-image: linear-gradient( 180deg , hsla(0, 0%, 100%, 0.8), hsla(0, 0%, 100%, 0)); 
}


 :root {--mermaid-font-zoom:1.25em ;} 
</style><title>MySQL学习笔记</title>
</head>
<body class='typora-export os-windows typora-export-show-outline typora-export-no-collapse-outline'><div class='typora-export-content'>
<div class="typora-export-sidebar"><div class="outline-content"><li class="outline-item-wrapper outline-h1"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#mysql学习笔记">MySQL学习笔记</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#目录">目录</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#下载链接">下载链接</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#数据库概述">数据库概述</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#sqldbdbms">SQL、DB、DBMS</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#表table）是什么">表（table）是什么</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#sql语句分类">SQL语句分类</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#导入数据">导入数据</a></div><ul class="outline-children"></ul></li></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#常用命令">常用命令</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#简单查询">简单查询</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#条件查询">条件查询</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#排序数据">排序数据</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#分组函数">分组函数</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#group-by-和-having">group by 和 having</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#一个完整的dql语句">一个完整的DQL语句</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#查询去重">查询去重</a></div><ul class="outline-children"></ul></li></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#连接查询">连接查询</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#笛卡尔积现象">笛卡尔积现象</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#外连接">外连接</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#多表连接">多表连接</a></div><ul class="outline-children"></ul></li></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#子查询">子查询</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#union和limit">union和limit</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#union">union</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#limit">limit</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#通用的标准分页sql">通用的标准分页SQL</a></div><ul class="outline-children"></ul></li></ul></li></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#查询技巧">查询技巧</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#增删改crud">增删改(CRUD)</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#ddl表的结构）">DDL（表的结构）</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#create">create</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#alter">alter</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#drop">drop</a></div><ul class="outline-children"></ul></li></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#dml表的内容）">DML（表的内容）</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#insert">insert</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h5"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#表的复制">表的复制</a></div><ul class="outline-children"></ul></li></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#update">update</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#delete">delete</a></div><ul class="outline-children"></ul></li></ul></li></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#约束">约束</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#非空约束">非空约束</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#唯一性约束">唯一性约束</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#主键约束">主键约束</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#外键约束">外键约束</a></div><ul class="outline-children"></ul></li></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#存储引擎">存储引擎*</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#存储引擎概述">存储引擎概述</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#常见的存储引擎">常见的存储引擎</a></div><ul class="outline-children"></ul></li></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#事务tcltransaction）">事务TCL（Transaction）</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#事务概述">事务概述</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#事务的特性">事务的特性</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#自动提交模式">自动提交模式</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#事务的隔离级别">事务的隔离级别</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#隔离级别">隔离级别</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#四个隔离级别">四个隔离级别</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#设置隔离级别">设置隔离级别</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#隔离级别的作用范围">隔离级别的作用范围</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#查看隔离级别">查看隔离级别</a></div><ul class="outline-children"></ul></li></ul></li></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#索引">索引</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#视图">视图</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#视图概述">视图概述</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#视图操作">视图操作</a></div><ul class="outline-children"></ul></li></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#dba命令">DBA命令</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#数据导出">数据导出</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#远程连接">远程连接</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#授权dcl）">授权（DCL）</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#用户授权">用户授权</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#刷新权限">刷新权限</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#查看用户权限">查看用户权限</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#回收权限">回收权限</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#删除用户">删除用户</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#用户重命名">用户重命名</a></div><ul class="outline-children"></ul></li><li class="outline-item-wrapper outline-h4"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#修改密码">修改密码</a></div><ul class="outline-children"></ul></li></ul></li></ul></li><li class="outline-item-wrapper outline-h2"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#数据库的设计三范式">数据库的设计三范式</a></div><ul class="outline-children"><li class="outline-item-wrapper outline-h3"><div class="outline-item"><span class="outline-expander"></span><a class="outline-label" href="#三范式总结">三范式总结</a></div><ul class="outline-children"></ul></li></ul></li></ul></li></div></div><div id='write'  class=''><h1 id='mysql学习笔记'><span>MySQL学习笔记</span></h1><h2 id='目录'><span>目录</span></h2><div class='md-toc' mdtype='toc'><p class="md-toc-content" role="list"><span role="listitem" class="md-toc-item md-toc-h1" data-ref="n0"><a class="md-toc-inner" href="#mysql学习笔记">MySQL学习笔记</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n2"><a class="md-toc-inner" href="#目录">目录</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n4"><a class="md-toc-inner" href="#下载链接">下载链接</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n14"><a class="md-toc-inner" href="#数据库概述">数据库概述</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n15"><a class="md-toc-inner" href="#sqldbdbms">SQL、DB、DBMS</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n27"><a class="md-toc-inner" href="#表table）是什么">表（table）是什么</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n40"><a class="md-toc-inner" href="#sql语句分类">SQL语句分类</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n52"><a class="md-toc-inner" href="#导入数据">导入数据</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n70"><a class="md-toc-inner" href="#常用命令">常用命令</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n90"><a class="md-toc-inner" href="#简单查询">简单查询</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n102"><a class="md-toc-inner" href="#条件查询">条件查询</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n158"><a class="md-toc-inner" href="#排序数据">排序数据</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n177"><a class="md-toc-inner" href="#分组函数">分组函数</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n218"><a class="md-toc-inner" href="#group-by-和-having">group by 和 having</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n240"><a class="md-toc-inner" href="#一个完整的dql语句">一个完整的DQL语句</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n242"><a class="md-toc-inner" href="#查询去重">查询去重</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n257"><a class="md-toc-inner" href="#连接查询">连接查询</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n292"><a class="md-toc-inner" href="#笛卡尔积现象">笛卡尔积现象</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n310"><a class="md-toc-inner" href="#外连接">外连接</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n340"><a class="md-toc-inner" href="#多表连接">多表连接</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n342"><a class="md-toc-inner" href="#子查询">子查询</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n355"><a class="md-toc-inner" href="#union和limit">union和limit</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n356"><a class="md-toc-inner" href="#union">union</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n364"><a class="md-toc-inner" href="#limit">limit</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n390"><a class="md-toc-inner" href="#通用的标准分页sql">通用的标准分页SQL</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n393"><a class="md-toc-inner" href="#查询技巧">查询技巧</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n407"><a class="md-toc-inner" href="#增删改crud">增删改(CRUD)</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n408"><a class="md-toc-inner" href="#ddl表的结构）">DDL（表的结构）</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n409"><a class="md-toc-inner" href="#create">create</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n456"><a class="md-toc-inner" href="#alter">alter</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n468"><a class="md-toc-inner" href="#drop">drop</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n477"><a class="md-toc-inner" href="#dml表的内容）">DML（表的内容）</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n478"><a class="md-toc-inner" href="#insert">insert</a></span><span role="listitem" class="md-toc-item md-toc-h5" data-ref="n502"><a class="md-toc-inner" href="#表的复制">表的复制</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n508"><a class="md-toc-inner" href="#update">update</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n518"><a class="md-toc-inner" href="#delete">delete</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n532"><a class="md-toc-inner" href="#约束">约束</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n551"><a class="md-toc-inner" href="#非空约束">非空约束</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n560"><a class="md-toc-inner" href="#唯一性约束">唯一性约束</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n571"><a class="md-toc-inner" href="#主键约束">主键约束</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n602"><a class="md-toc-inner" href="#外键约束">外键约束</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n618"><a class="md-toc-inner" href="#存储引擎">存储引擎*</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n619"><a class="md-toc-inner" href="#存储引擎概述">存储引擎概述</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n634"><a class="md-toc-inner" href="#常见的存储引擎">常见的存储引擎</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n674"><a class="md-toc-inner" href="#事务tcltransaction）">事务TCL（Transaction）</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n675"><a class="md-toc-inner" href="#事务概述">事务概述</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n699"><a class="md-toc-inner" href="#事务的特性">事务的特性</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n722"><a class="md-toc-inner" href="#自动提交模式">自动提交模式</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n751"><a class="md-toc-inner" href="#事务的隔离级别">事务的隔离级别</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n752"><a class="md-toc-inner" href="#隔离级别">隔离级别</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n765"><a class="md-toc-inner" href="#四个隔离级别">四个隔离级别</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n782"><a class="md-toc-inner" href="#设置隔离级别">设置隔离级别</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n821"><a class="md-toc-inner" href="#隔离级别的作用范围">隔离级别的作用范围</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n842"><a class="md-toc-inner" href="#查看隔离级别">查看隔离级别</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n861"><a class="md-toc-inner" href="#索引">索引</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n905"><a class="md-toc-inner" href="#视图">视图</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n906"><a class="md-toc-inner" href="#视图概述">视图概述</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n930"><a class="md-toc-inner" href="#视图操作">视图操作</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n941"><a class="md-toc-inner" href="#dba命令">DBA命令</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n942"><a class="md-toc-inner" href="#数据导出">数据导出</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n960"><a class="md-toc-inner" href="#远程连接">远程连接</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n962"><a class="md-toc-inner" href="#授权dcl）">授权（DCL）</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n963"><a class="md-toc-inner" href="#用户授权">用户授权</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n978"><a class="md-toc-inner" href="#刷新权限">刷新权限</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n980"><a class="md-toc-inner" href="#查看用户权限">查看用户权限</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n982"><a class="md-toc-inner" href="#回收权限">回收权限</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n984"><a class="md-toc-inner" href="#删除用户">删除用户</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n986"><a class="md-toc-inner" href="#用户重命名">用户重命名</a></span><span role="listitem" class="md-toc-item md-toc-h4" data-ref="n988"><a class="md-toc-inner" href="#修改密码">修改密码</a></span><span role="listitem" class="md-toc-item md-toc-h2" data-ref="n990"><a class="md-toc-inner" href="#数据库的设计三范式">数据库的设计三范式</a></span><span role="listitem" class="md-toc-item md-toc-h3" data-ref="n1011"><a class="md-toc-inner" href="#三范式总结">三范式总结</a></span></p></div><h2 id='下载链接'><span>下载链接</span></h2><ul><li><a href='https://dev.mysql.com/downloads/mysql/'><span>MySQL</span></a></li><li><a href='https://navicat.com.cn/products'><span>Navicat</span></a></li><li><a href='https://github.com/dbeaver/dbeaver'><span>DBeaver</span></a></li><li><a href='https://blog.csdn.net/u013235478/article/details/50623693'><span>MySQL安装教程</span></a></li></ul><h2 id='数据库概述'><span>数据库概述</span></h2><h3 id='sqldbdbms'><span>SQL、DB、DBMS</span></h3><ul><li><span>DB:DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）</span></li><li><span>DBMS: DataBase Management System（数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer...）</span></li><li><span>SQL: 结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。（sql语句的编译由DBMS完成。）</span></li><li><span>DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。</span></li><li><span>DBMS -(执行)-&gt; SQL -(操作)-&gt; DB</span></li></ul><h3 id='表table）是什么'><span>表（table）是什么</span></h3><ul><li><p><span>table</span></p></li><li><p><span>table是数据库的基本组成单元，所有的数据都以表的形式组织，目的是可读性强。</span></p></li><li><p><span>一个表可以包括行和列</span></p><ul><li><span>行：被称为数据、记录(data)</span></li><li><span>列：被称为字段(column)字段名、数据类型、相关的约束。</span></li></ul></li></ul><h3 id='sql语句分类'><span>SQL语句分类</span></h3><ol start='' ><li><span>DQL（数据查询语言）：查询语句，凡是select语句都是DQL。</span></li><li><span>DML（数据操作语言）：insert delete update，对表当中的数据进行增删改。</span></li><li><span>DDL（数据定义语言）：create drop alter，对表结构的增删改。</span></li><li><span>TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Transaction)</span></li><li><span>DCL（数据控制语言）：grant授权、revoke撤销权限等。</span></li></ol><h3 id='导入数据'><span>导入数据</span></h3><ol start='' ><li><span>登录：</span><code>mysql -uroot -p</code></li><li><span>查看有哪些数据库：</span><code>show databases;</code></li><li><span>创建属于自己的数据库：</span><code>create database bjpowernode;</code></li><li><span>使用自己刚刚创建的数据库：</span><code>use bjpowernode;</code></li><li><span>查看数据库中的表：</span><code>show tables;</code></li><li><span>写入数据：</span><code>source Path</code></li><li><span>删除数据库：</span><code>drop database bjpowernode;</code></li><li><span>这些都不是SQL语句，属于MySQL自己的命令。</span></li></ol><h2 id='常用命令'><span>常用命令</span></h2><ol start='' ><li><code>select database();</code><span> 查看当前使用的数据库</span></li><li><code>select version();</code><span> 查看当前MySQL版本</span></li><li><code>\c</code><span> 结束当前语句</span></li><li><code>exit</code><span>、</span><code>quit</code><span>、</span><code>\q</code><span> 退出MySQL</span></li><li><code>show create database 数据库名;</code><span>查看数据库的创建语句</span></li><li><code>show create table 表名</code><span> 查看表的创建语句</span></li><li><code>show tables from 表名;</code><span> 查看其它数据库中的表</span></li><li><code>desc 表名;</code><span> 查看表的结构</span></li><li><code>drop table 表名</code><span>删除表</span></li></ol><h2 id='简单查询'><span>简单查询</span></h2><ol start='' ><li><code>select 字段 form 表名;</code><span>查询一个字段</span></li><li><code>select 字段名1,字段名2,字段名3,.... from 表名;</code><span> 查询多个字段</span></li><li><code>select empno, ename, sal*12 from emp;</code><span> 查询时可以执行运算</span></li><li><code>select * from emp;</code><span> 查询所有字段（开发中不建议使用，因为效率比较低）</span></li><li><code>select empno as ‘员工编号’, ename as ‘员工姓名’, sal*12 as ‘年薪’ from emp;</code><span> as 可以用来重命名，可以省略，</span><strong><span>中文和字符串用</span><code>&#39;&#39;</code></strong></li></ol><h2 id='条件查询'><span>条件查询</span></h2><figure><table><thead><tr><th><span>运算符</span></th><th><span>说明</span></th></tr></thead><tbody><tr><td><span>=</span></td><td><span>等于</span></td></tr><tr><td><span>&lt;&gt;或!=</span></td><td><span>不等于</span></td></tr><tr><td><span>&lt;</span></td><td><span>小于</span></td></tr><tr><td><span>&lt;=</span></td><td><span>小于等于</span></td></tr><tr><td><span>&gt;</span></td><td><span>大于</span></td></tr><tr><td><span>&gt;=</span></td><td><span>大于等于</span></td></tr><tr><td><span>between...and...</span></td><td><span>两个值之间（等同于 &gt;= and &lt;= ）闭区间左小右大</span></td></tr><tr><td><span>is null</span></td><td><span>为 null （is not null 不为空）</span></td></tr><tr><td><span>and</span></td><td><span>并且（与）</span></td></tr><tr><td><span>or</span></td><td><span>或者（或）</span></td></tr><tr><td><span>in</span></td><td><span>包含</span></td></tr><tr><td><span>not</span></td><td><span>取非（用在 is 和 in 中）</span></td></tr><tr><td><span>like</span></td><td><span>模糊查询(&#39;_&#39;单个字符、&#39;%&#39;多个字符)</span></td></tr></tbody></table></figure><ul><li><p><span>语法： </span><code>select 字段 from 数据 where 条件</code></p></li><li><p><code>between ... and ...</code><span>可以用在字符串方面，左闭右开</span></p></li><li><p><span>NULL不等于0</span></p></li><li><p><span>任意数与NULL做任意运算，结果都是NULL</span></p><ul><li><span>解决方法：</span><strong><span>空处理函数</span></strong><span>：</span><code>ifnull(可能为NULL的字段，代替的值)</code><span> </span></li></ul></li></ul><h2 id='排序数据'><span>排序数据</span></h2><ul><li><p><span>语法：</span></p><ul><li><code>select 字段 from 数据 order by 字段;</code></li><li><code>select 字段 from 数据 where 条件 order by 字段;</code><span> 默认是升序</span></li><li><code>select 字段 from 数据 where 条件 order by 字段 asc;</code><span> 升序</span></li><li><code>select 字段 from 数据 where 条件 order by 字段 desc;</code><span> 降序</span></li><li><code>select 字段 from 数据 where 条件 order by 字段 desc,字段 desc;</code><span> 多个排序条件</span></li></ul></li><li><p><span>可以用数字表示排序第几列</span><code>select * from emp order by 5;</code></p></li><li><p><span>多个排序时越靠前的字段越有主导作用，只有当前字段的数据一样时，才会按照后面的字段排序。</span></p></li></ul><h2 id='分组函数'><span>分组函数</span></h2><ul><li><p><code>count()</code><span> 计数</span></p></li><li><p><code>sum()</code><span> 求和</span></p></li><li><p><code>avg()</code><span> 取平均值</span></p></li><li><p><code>max()</code><span> 取最大值</span></p></li><li><p><code>min()</code><span> 取最小值</span></p></li><li><p><span>分组函数自动忽略空值NULL，不需要手动的加where条件排除空值。</span></p></li><li><p><code>select sum(sal) from emp;</code><span> 不为NULL的薪资的总和</span></p></li><li><p><strong><span>分组函数不能用在where语句中。</span></strong></p></li><li><p><span>可以将这些组合函数都放到select中一起使用 </span><code>select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;</code></p></li><li><p><span>count(*)和count(具体的某个字段)，他们有什么区别？</span></p><ul><li><span>count(*):不是统计某个字段中数据的个数，而是统计总记录条数。包含NULL（和某个字段无关）</span></li><li><span>count(comm): 表示统计comm字段中不为NULL的数据总数量。</span></li><li><span>count(1): ?</span></li></ul></li><li><p><span>多行处理函数：若干行数据一起处理</span></p><ul><li><span>分组函数</span></li></ul></li><li><p><span>单行处理函数：一行一行的处理</span></p><ul><li><code>ifnull()</code></li></ul></li></ul><h3 id='group-by-和-having'><span>group by 和 having</span></h3><ul><li><p><span>group by：按照某个字段或者某些字段进行分组。</span></p><ul><li><span>分组函数一般都会和</span><code>group by</code><span>联合使用</span></li><li><span>任何一个分组函数都是在</span><code>group by</code><span>之后执行的</span></li><li><span>当一条SQL语句没有用</span><code>group by</code><span>的话，整张表会自成一组。</span></li><li><strong><span>当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。</span></strong></li><li><span>多个字段可以联合一起分组。</span></li></ul></li><li><p><span>having：是对分组之后的数据进行再次过滤。</span></p><ul><li><span>能用where过滤的话，尽量使用where。</span></li><li><span>如果没有使用</span><code>group by</code><span>的话，不能使用</span><code>having</code><span>。</span></li></ul></li></ul><h3 id='一个完整的dql语句'><span>一个完整的DQL语句</span></h3><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="sql" style="break-inside: unset;"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><span><span>​</span>x</span></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span><span class="cm-tab" role="presentation" cm-text="	">  </span> &nbsp;  <span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-number">5</span></span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>...</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">from</span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-number">1</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" class="cm-tab-wrap-hack" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>...<span class="cm-tab" role="presentation" cm-text="	"> </span><span class="cm-tab" role="presentation" cm-text="	">    </span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">where</span><span class="cm-tab" role="presentation" cm-text="	">   </span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-number">2</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" class="cm-tab-wrap-hack" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>...<span class="cm-tab" role="presentation" cm-text="	"> </span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">group</span> <span class="cm-keyword">by</span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-number">3</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>...</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">having</span><span class="cm-tab" role="presentation" cm-text="	">  </span> &nbsp;  <span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-number">4</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>...</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">order</span> <span class="cm-keyword">by</span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-number">6</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>...</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">limit</span><span class="cm-tab" role="presentation" cm-text="	">   </span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-number">7</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>...<span class="cm-punctuation">;</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="">​</span></span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 336px;"></div><div class="CodeMirror-gutters" style="display: none; height: 336px;"></div></div></div></pre><h3 id='查询去重'><span>查询去重</span></h3><ul><li><p><code>distinct</code><span>关键字</span></p></li><li><p><code>mysql&gt; select distinct job from emp;</code></p></li><li><p><span>mysql&gt; select ename,distinct job from emp;</span></p><ul><li><span>以上的sql语句是错误的，</span><strong><span>distinct只能出现在所有字段的最前面。</span></strong><span>（联合去重）</span></li><li><span>mysql&gt; select distinct deptno,job from emp;</span></li></ul></li><li><p><span>统计岗位的数量？</span><code>select count(distinct job) from emp;</code></p></li></ul><h2 id='连接查询'><span>连接查询</span></h2><ul><li><p><span>在实际开发中，大部分情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终结果。</span></p></li><li><p><span>连接查询的分类</span></p><ul><li><p><span>根据年代来分</span></p><ul><li><span>SQL92</span></li><li><span>SQL99</span></li></ul></li><li><p><span>根据连接方式</span></p><ul><li><p><span>内连接</span></p><ul><li><span>等值连接（连接条件是等量关系）</span></li><li><span>非等值连接（连接条件是非等量关系）</span></li><li><span>自连接（一张表看做两张表。自己连接自己）</span></li></ul></li><li><p><span>外连接</span></p><ul><li><span>左外连接</span></li><li><span>右外连接</span></li></ul></li><li><p><span>全连接</span></p></li></ul></li></ul></li></ul><h3 id='笛卡尔积现象'><span>笛卡尔积现象</span></h3><ul><li><p><code>select ename,dname from emp,dept;</code></p></li><li><p><span>别名方式：</span><code>select e.ename,d.dname from emp e,dept d;</code></p></li><li><p><span>在表的连接查询方面有一种现象被称为：笛卡尔积现象。（笛卡尔乘积现象）</span></p></li><li><p><strong><span>笛卡尔积现象</span></strong><span>：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。</span></p></li><li><p><span>笛卡尔现象的避免</span></p><ul><li><span>加条件过滤</span></li><li><strong><span>使用条件过滤避免笛卡尔现象，匹配次数并没有减少，并不会提高查询效率</span></strong></li></ul></li></ul><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="mysql" style="break-inside: unset;"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="mysql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" class="cm-tab-wrap-hack" style="padding-right: 0.1px;"><span class="cm-keyword">select</span><span class="cm-tab" role="presentation" cm-text="	">  </span></span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>e<span class="cm-variable-2">.ename</span><span class="cm-punctuation">,</span>d<span class="cm-variable-2">.dname</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">from</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>emp e <span class="cm-punctuation">,</span> dept d</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">where</span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // 过滤条件</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>e<span class="cm-variable-2">.deptno</span> <span class="cm-operator">=</span> d<span class="cm-variable-2">.deptno</span><span class="cm-punctuation">;</span> //SQL92，以后不用。联接关系</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">//<span class="cm-operator">-------------------------------------------------------</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>e<span class="cm-variable-2">.ename</span><span class="cm-punctuation">,</span>d<span class="cm-variable-2">.dname</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">from</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>emp e</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">inner</span> <span class="cm-keyword">join</span> &nbsp; // <span class="cm-keyword">inner</span>可以省略的，带着inner目的是可读性好一些。</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>dept d</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">on</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>e<span class="cm-variable-2">.deptno</span> <span class="cm-operator">=</span> d<span class="cm-variable-2">.deptno</span><span class="cm-punctuation">;</span> &nbsp; &nbsp; // 联接关系，过滤条件</span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 381px;"></div><div class="CodeMirror-gutters" style="display: none; height: 381px;"></div></div></div></pre><h3 id='外连接'><span>外连接</span></h3><ul><li><p><span>什么是外连接？和内连接有什么区别？</span></p><ul><li><strong><span>内连接</span></strong><span>在查询的结果中只有匹配成功的，只有符合匹配条件的数据，内连接的两张表没有主副之分，两张表是平等的。</span></li><li><strong><span>外连接</span></strong><span>的表中有主表，有副表，主要查询主表中的内容，捎带查询副表。主表中的所有数据</span><strong><span>一定</span></strong><span>会被查询出来，当副表中没有数据和主表匹配，会自动用NULL与之匹配。</span></li><li><span>外连接的SQL语句上有left和right，用来指定主表，内连接没有。</span></li></ul></li><li><p><span>外连接的分类</span></p><ul><li><span>左外连接（左边是主表）</span></li><li><span>右外连接（右边是主表）</span></li><li><span>左右外连接的写法可以互相转换</span></li></ul></li><li><p><span>语法：</span></p><ul><li><span>内连接：</span><code>select 字段... from 表1 inner join 表2 on 过滤条件</code></li><li><span>外连接：</span><code>select 字段... from 表1 left/right outer join 表2 on 过滤条件</code></li><li><code>inner</code><span>和</span><code>outer</code><span>可以省略</span></li></ul></li></ul><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="mysql"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="mysql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.19971px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> d.<span class="cm-operator">*</span> <span class="cm-keyword">from</span> emp e <span class="cm-keyword">right</span> <span class="cm-keyword">outer</span> <span class="cm-keyword">join</span> dept d <span class="cm-keyword">on</span> d<span class="cm-variable-2">.deptno</span><span class="cm-operator">=</span>e<span class="cm-variable-2">.deptno</span> <span class="cm-keyword">where</span> e<span class="cm-variable-2">.ename</span> <span class="cm-keyword">is</span> <span class="cm-atom">null</span><span class="cm-punctuation">;</span></span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+--------+------------+--------+</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> DEPTNO <span class="cm-operator">|</span> DNAME &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> LOC &nbsp; &nbsp;<span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+--------+------------+--------+</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">40</span> <span class="cm-operator">|</span> OPERATIONS <span class="cm-operator">|</span> BOSTON <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+--------+------------+--------+</span></span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 157px;"></div><div class="CodeMirror-gutters" style="display: none; height: 157px;"></div></div></div></pre><h3 id='多表连接'><span>多表连接</span></h3><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="mysql" style="break-inside: unset;"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="mysql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">找出每一个员工的部门名称以及工资等级。</span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql<span class="cm-operator">&gt;</span> <span class="cm-keyword">select</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  e<span class="cm-variable-2">.ename</span><span class="cm-punctuation">,</span>d<span class="cm-variable-2">.dname</span><span class="cm-punctuation">,</span>e<span class="cm-variable-2">.sal</span><span class="cm-punctuation">,</span>s<span class="cm-variable-2">.grade</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">from</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  emp e </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">left</span> <span class="cm-keyword">join</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  dept d </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">on</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  e<span class="cm-variable-2">.deptno</span><span class="cm-operator">=</span>d<span class="cm-variable-2">.deptno</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">left</span> <span class="cm-keyword">join</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  salgrade s </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">on</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  e<span class="cm-variable-2">.sal</span> <span class="cm-keyword">between</span> losal <span class="cm-keyword">and</span> hisal<span class="cm-punctuation">;</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="">​</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+--------+------------+---------+-------+</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> ename &nbsp;<span class="cm-operator">|</span> dname &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> sal &nbsp; &nbsp; <span class="cm-operator">|</span> grade <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+--------+------------+---------+-------+</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> SMITH &nbsp;<span class="cm-operator">|</span> RESEARCH &nbsp; <span class="cm-operator">|</span> &nbsp;<span class="cm-number">800.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">1</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> ALLEN &nbsp;<span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> <span class="cm-number">1600.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">3</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> WARD &nbsp; <span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> <span class="cm-number">1250.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">2</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> JONES &nbsp;<span class="cm-operator">|</span> RESEARCH &nbsp; <span class="cm-operator">|</span> <span class="cm-number">2975.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">4</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> MARTIN <span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> <span class="cm-number">1250.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">2</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> BLAKE &nbsp;<span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> <span class="cm-number">2850.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">4</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> CLARK &nbsp;<span class="cm-operator">|</span> ACCOUNTING <span class="cm-operator">|</span> <span class="cm-number">2450.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">4</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> SCOTT &nbsp;<span class="cm-operator">|</span> RESEARCH &nbsp; <span class="cm-operator">|</span> <span class="cm-number">3000.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">4</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> KING &nbsp; <span class="cm-operator">|</span> ACCOUNTING <span class="cm-operator">|</span> <span class="cm-number">5000.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">5</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> TURNER <span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> <span class="cm-number">1500.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">3</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> ADAMS &nbsp;<span class="cm-operator">|</span> RESEARCH &nbsp; <span class="cm-operator">|</span> <span class="cm-number">1100.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">1</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> JAMES &nbsp;<span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> &nbsp;<span class="cm-number">950.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">1</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> FORD &nbsp; <span class="cm-operator">|</span> RESEARCH &nbsp; <span class="cm-operator">|</span> <span class="cm-number">3000.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">4</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> MILLER <span class="cm-operator">|</span> ACCOUNTING <span class="cm-operator">|</span> <span class="cm-number">1300.00</span> <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">2</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+--------+------------+---------+-------+</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="">​</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="">​</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">找出每一个员工的部门名称、工资等级、以及上级领导。</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql<span class="cm-operator">&gt;</span> <span class="cm-keyword">select</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  e<span class="cm-variable-2">.ename</span><span class="cm-punctuation">,</span>d<span class="cm-variable-2">.dname</span><span class="cm-punctuation">,</span>s<span class="cm-variable-2">.grade</span><span class="cm-punctuation">,</span>e1<span class="cm-variable-2">.ename</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">from</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  emp e </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">left</span> <span class="cm-keyword">join</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  dept d </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">on</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  d<span class="cm-variable-2">.deptno</span><span class="cm-operator">=</span>e<span class="cm-variable-2">.deptno</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">left</span> <span class="cm-keyword">join</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  salgrade s </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">on</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  e<span class="cm-variable-2">.sal</span> <span class="cm-keyword">between</span> s<span class="cm-variable-2">.losal</span> <span class="cm-keyword">and</span> hisal </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">left</span> <span class="cm-keyword">join</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  emp e1 </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-keyword">on</span> </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  e1<span class="cm-variable-2">.empno</span><span class="cm-operator">=</span>e<span class="cm-variable-2">.mgr</span><span class="cm-punctuation">;</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="">​</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+--------+------------+-------+-------+</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> ename &nbsp;<span class="cm-operator">|</span> dname &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> grade <span class="cm-operator">|</span> ename <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+--------+------------+-------+-------+</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> SMITH &nbsp;<span class="cm-operator">|</span> RESEARCH &nbsp; <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">1</span> <span class="cm-operator">|</span> FORD &nbsp;<span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> ALLEN &nbsp;<span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">3</span> <span class="cm-operator">|</span> BLAKE <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> WARD &nbsp; <span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">2</span> <span class="cm-operator">|</span> BLAKE <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> JONES &nbsp;<span class="cm-operator">|</span> RESEARCH &nbsp; <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">4</span> <span class="cm-operator">|</span> KING &nbsp;<span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> MARTIN <span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">2</span> <span class="cm-operator">|</span> BLAKE <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> BLAKE &nbsp;<span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">4</span> <span class="cm-operator">|</span> KING &nbsp;<span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> CLARK &nbsp;<span class="cm-operator">|</span> ACCOUNTING <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">4</span> <span class="cm-operator">|</span> KING &nbsp;<span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> SCOTT &nbsp;<span class="cm-operator">|</span> RESEARCH &nbsp; <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">4</span> <span class="cm-operator">|</span> JONES <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> KING &nbsp; <span class="cm-operator">|</span> ACCOUNTING <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">5</span> <span class="cm-operator">|</span> <span class="cm-atom">NULL</span> &nbsp;<span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> TURNER <span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">3</span> <span class="cm-operator">|</span> BLAKE <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> ADAMS &nbsp;<span class="cm-operator">|</span> RESEARCH &nbsp; <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">1</span> <span class="cm-operator">|</span> SCOTT <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> JAMES &nbsp;<span class="cm-operator">|</span> SALES &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">1</span> <span class="cm-operator">|</span> BLAKE <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> FORD &nbsp; <span class="cm-operator">|</span> RESEARCH &nbsp; <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">4</span> <span class="cm-operator">|</span> JONES <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> MILLER <span class="cm-operator">|</span> ACCOUNTING <span class="cm-operator">|</span> &nbsp; &nbsp; <span class="cm-number">2</span> <span class="cm-operator">|</span> CLARK <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+--------+------------+-------+-------+</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="">​</span></span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 1590px;"></div><div class="CodeMirror-gutters" style="display: none; height: 1590px;"></div></div></div></pre><h2 id='子查询'><span>子查询</span></h2><ul><li><p><span>select语句中嵌套select语句，被嵌套的select语句就是子查询。</span></p></li><li><p><span>子查询可以出现在哪里？</span></p></li><li><p><code>select ..(select).. from ..(select).. where ..(select)..</code></p></li><li><p><span>在from 后用子查询可以把子查询的结果看成一张新表，可以重命名。</span></p><ul><li><code>select t.xxx from (select语句) t where 条件</code></li></ul></li></ul><h2 id='union和limit'><span>union和limit</span></h2><h3 id='union'><span>union</span></h3><ul><li><span>可以合并集合,可以将查询的两张表连接。</span></li><li><code>select * from emp where job=&#39;MANAGER&#39; union select * from emp where job=&#39;SALESMAN&#39;</code></li><li><span>合并结果集的时候，需要查询字段对应个数相同。在Oracle中更严格，不但要求个数相同，</span></li></ul><h3 id='limit'><span>limit</span></h3><ul><li><p><span>limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）</span></p></li><li><p><span>limit取结果集中的部分数据，这是它的作用。</span></p></li><li><p><span>limit是sql语句最后执行的一个环节。</span></p></li><li><p><span>语法：</span></p><ul><li><code>select 字段 from 表 oeder by 字段 asc/desc limit startIndex, length</code></li><li><code>select 字段 from 表 oeder by 字段 asc/desc limit length</code></li><li><span>如果没有起始位默认从0开始。</span></li></ul></li></ul><p><span>delete 后面是支持 limit 关键字的，但仅支持单个参数，也就是 [limit row_count]，用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。  </span></p><p><span>有什么用？</span></p><ol start='' ><li><span>降低写错SQL的代价，就算删错了，比如limit 1,那也就丢了1条数据，并不致命，通过binlog也可以很快恢复数据。</span></li><li><span>对于单条删除和更新操作，在 delete 和 update 后面加 limit 1，假如第一条就命中了删除行，这时就 return 了，否则还会执行完全表扫描才 return。</span></li><li><code>delete from t where sex = 1 limit 1;</code></li></ol><h4 id='通用的标准分页sql'><span>通用的标准分页SQL</span></h4><p><span>每页显示pageSize条记录：</span><br/><span>第pageNo页：(pageNo - 1) * pageSize, pageSize</span><br/><span>java代码  </span></p><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="java"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="java"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">{</span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-variable-3">int</span> <span class="cm-variable">pageNo</span> <span class="cm-operator">=</span> <span class="cm-number">2</span>; <span class="cm-comment">// 页码是2</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-variable-3">int</span> <span class="cm-variable">pageSize</span> <span class="cm-operator">=</span> <span class="cm-number">10</span>; <span class="cm-comment">// 每页显示10条</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" class="cm-tab-wrap-hack" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-variable">limit</span> (<span class="cm-variable">pageNo</span> <span class="cm-operator">-</span> <span class="cm-number">1</span>) <span class="cm-operator">*</span> <span class="cm-variable">pageSize</span>, <span class="cm-variable">pageSize</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">}</span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 134px;"></div><div class="CodeMirror-gutters" style="display: none; height: 134px;"></div></div></div></pre><h2 id='查询技巧'><span>查询技巧</span></h2><ol start='' ><li><p><span>第几多</span></p><ul><li><span>去重，升序排序，用limit</span></li></ul></li><li><p><span>第二多</span></p><ul><li><span>找出第一，从小于第一中找最大值</span></li></ul></li><li><p>&nbsp;</p></li></ol><h2 id='增删改crud'><span>增删改(CRUD)</span></h2><h3 id='ddl表的结构）'><span>DDL（表的结构）</span></h3><h4 id='create'><span>create</span></h4><p><span>语法：  </span></p><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="mysql"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="mysql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">create</span> <span class="cm-keyword">table</span> 表名<span class="cm-bracket">(</span></span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>字段名1 数据类型<span class="cm-bracket">(</span>长度限制<span class="cm-bracket">)</span> <span class="cm-keyword">default</span> 默认值 列级约束<span class="cm-punctuation">,</span> // 可不写</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>字段名2 数据类型<span class="cm-bracket">(</span>长度限制<span class="cm-bracket">)</span><span class="cm-punctuation">,</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>字段名3 数据类型<span class="cm-bracket">(</span>长度限制<span class="cm-bracket">)</span><span class="cm-punctuation">,</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>....</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>表级约束<span class="cm-bracket">(</span>字段名<span class="cm-punctuation">,...</span><span class="cm-bracket">)</span> // 可不写</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-bracket">)</span> <span class="cm-keyword">engine</span><span class="cm-operator">=</span>存储引擎 <span class="cm-string-2">charset</span><span class="cm-operator">=</span>字符集 <span class="cm-keyword">comment</span><span class="cm-operator">=</span><span class="cm-string">'说明注释'</span> //可不写</span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 202px;"></div><div class="CodeMirror-gutters" style="display: none; height: 202px;"></div></div></div></pre><p><strong><span>MySQL常用数据类型</span></strong></p><figure><table><thead><tr><th><span>类型</span></th><th><span>描述</span></th></tr></thead><tbody><tr><td><span>char(长度)</span></td><td><span>定长字符串</span></td></tr><tr><td><span>varchar()</span></td><td><span>变长字符串</span></td></tr><tr><td><span>double(有效数字位数，小数位)</span></td><td><span>数值型</span></td></tr><tr><td><span>float(有效数字位数，小数位)</span></td><td><span>数值型</span></td></tr><tr><td><span>int( 长度)</span></td><td><span>整型</span></td></tr><tr><td><span>bigint(长度)</span></td><td><span>长整型</span></td></tr><tr><td><span>Date</span></td><td><span>日期型 年月日</span></td></tr><tr><td><span>DateTime</span></td><td><span>日期型 年月日 时分秒 毫秒</span></td></tr><tr><td><span>time</span></td><td><span>日期型 时分秒</span></td></tr><tr><td><span>BLOB</span></td><td><span>Binary Large OBject（二进制大对象）</span></td></tr><tr><td><span>CLOB</span></td><td><span>Character Large OBject（字符大对象）</span></td></tr><tr><td><span>enum(数据,数据,...)</span></td><td><span>枚举</span></td></tr><tr><td><span>其它…………………</span></td><td>&nbsp;</td></tr></tbody></table></figure><h4 id='alter'><span>alter</span></h4><ul><li><span>-------修改表名：</span><code>alter table 表名 rename to 新表名;</code></li><li><span>-----修改字段名：</span><code>alter table 表名 change 字段名 新字段名 新字段类型;</code></li><li><span>修字段数据类型：</span><code>alter table 表名 modify 字段名 新字段类型;</code></li><li><span>-------增加字段：</span><code>alter table 表名 add 字段名 字段类型;</code></li><li><span>-------删除字段：</span><code>alter table 表名 drop 字段名;</code></li></ul><h4 id='drop'><span>drop</span></h4><ul><li><p><span>删除表</span></p><ul><li><code>drop table 表名;</code><span> // 这个通用。</span></li><li><code>drop table if exists 表名;</code><span> // 如果存在删除，oracle不支持这种写法。</span></li></ul></li></ul><h3 id='dml表的内容）'><span>DML（表的内容）</span></h3><h4 id='insert'><span>insert</span></h4><ul><li><p><span>语法：</span></p><ol start='' ><li><p><code>insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)</code></p><ul><li><span>字段和写入值的数里相同</span></li></ul></li><li><p><code>insert into 表名 values(值1,值2,值3,....)</code></p><ul><li><span>字段可以省略不写，values中的数据数量与顺序必须和原表相同</span></li></ul></li><li><p><code>insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....),(值1,值2,值3,....)</code></p><ul><li><span>可以同时插入多行数据</span></li></ul></li></ol></li><li><p><span>如果插入的数据不全，会自动补NULL。</span></p></li><li><p><span>当一条insert语句执行后，表中必然会多一行数据。无法用insert修改单行中的单个数据。</span></p></li></ul><h5 id='表的复制'><span>表的复制</span></h5><ul><li><code>create table 表名 as select语句;</code><span>将查询结果当做表创建出来。</span></li><li><code>insert into 表名 select语句</code><span>查询结果插入到一张表中</span></li></ul><h4 id='update'><span>update</span></h4><ul><li><span>语法：</span></li><li><code>update 表名 set 字段名1=值1,字段名2=值2... where 条件;</code></li><li><code>update 表名 set 字段名1=值1,字段名2=值2...;</code></li><li><span>没有条件会将整个表全部更新。</span></li></ul><h4 id='delete'><span>delete</span></h4><ul><li><p><span>语法：</span></p><ul><li><code>delete from 表名 where 条件;</code></li><li><code>delete from dept1;</code></li></ul></li><li><p><span>删除大表中的数据？（重点）</span></p><ul><li><code>truncate table 表名;</code><span> // 表被截断，不可回滚。永久丢失。</span></li></ul></li></ul><h2 id='约束'><span>约束</span></h2><ul><li><p><span>在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。</span></p></li><li><p><span>约束有</span><strong><span>列级约束</span></strong><span>和</span><strong><span>表级约束</span></strong></p></li><li><p><span>常见的约束有哪些呢？</span></p><ul><li><span>非空约束(not null)：约束的字段不能为NULL</span></li><li><span>唯一约束(unique)：约束的字段不能重复</span></li><li><span>主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）</span></li><li><span>外键约束(foreign key)：...（简称FK）</span></li><li><span>检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束（好像最新的支持了），但不会报错。(</span><a href='https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html' target='_blank' class='url'>https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html</a><span>)</span></li></ul></li></ul><h3 id='非空约束'><span>非空约束</span></h3><ul><li><code>not null</code></li><li><span>针对某个字段insert时，其值不能为空。</span></li><li><span>not null约束只有列级约束。没有表级约束。</span></li></ul><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="mysql"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="mysql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">drop</span> <span class="cm-keyword">table</span> <span class="cm-keyword">if</span> <span class="cm-keyword">exists</span> t_stu<span class="cm-punctuation">;</span></span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">create</span> <span class="cm-keyword">table</span> t_stu<span class="cm-bracket">(</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>id <span class="cm-builtin">int</span><span class="cm-bracket">(</span><span class="cm-number">2</span><span class="cm-bracket">)</span> <span class="cm-keyword">not</span> <span class="cm-atom">null</span><span class="cm-punctuation">,</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>name <span class="cm-builtin">varchar</span><span class="cm-bracket">(</span><span class="cm-number">255</span><span class="cm-bracket">)</span><span class="cm-punctuation">,</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>sex <span class="cm-builtin">char</span><span class="cm-bracket">(</span><span class="cm-number">1</span><span class="cm-bracket">)</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-bracket">)</span></span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 134px;"></div><div class="CodeMirror-gutters" style="display: none; height: 134px;"></div></div></div></pre><h3 id='唯一性约束'><span>唯一性约束</span></h3><ul><li><code>unique</code></li><li><code>unique(字段1,字段2, ...)</code></li><li><span>唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。</span></li><li><span>支持列级约束和表级约束</span></li></ul><h3 id='主键约束'><span>主键约束</span></h3><ul><li><p><code>primary key</code></p></li><li><p><code>primary key(字段1,字段2, ...)</code></p></li><li><p><span>主键约束的字段不能为空，也不能重复。</span></p></li><li><p><span>主键的作用：主键值是这行记录在这张表当中的唯一标识。</span></p></li><li><p><strong><span>一张表的主键约束只能有1个</span></strong></p></li><li><p><span>主键的分类</span></p><ul><li><p><span>根据字段数量分</span></p><ul><li><span>单一主键（推荐使用）</span></li><li><span>复合（联合）主键(多个字段联合起来添加一个主键约束起来添加一个主键约束 ，不建议使用，因为复合主键违背三范式。)</span></li></ul></li><li><p><span>根据主键性质分</span></p><ul><li><span>自然主键：主键值最好就是一个和业务没有任何关系的自然数</span></li><li><span>业务主键：主键值和系统的业务挂钩（身份证号、银行卡号）</span></li></ul></li></ul></li><li><p><strong><span>MySQL提供主键值自增类型</span></strong><span>：列级</span><code>auto_increment</code></p></li></ul><h3 id='外键约束'><span>外键约束</span></h3><ul><li><code>foreign key(外键字段) references 父表(父表主键字段)</code></li><li><span>外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键</span></li><li><span>存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表</span></li><li><span>外键值可以为NULL</span></li><li><span>删除数据的时候，先删除子表，再删除父表。添加数据的时候，先添加父表，在添加子表。创建表的时候，先创建父表，再创建子表。删除表的时候，先删除子表，在删除父表。</span></li><li><strong><span>被引用的字段不一定是主键，但至少具有unique约束。</span></strong></li></ul><h2 id='存储引擎'><span>存储引擎*</span></h2><h3 id='存储引擎概述'><span>存储引擎概述</span></h3><ul><li><p><span>查看当前mysql支持的存储引擎：</span><code>show engines \G</code></p></li><li><p><span>完整的建表语句</span></p><ul><li><code>CREATE TABLE</code><span>t_x</span><code>(</code><span>id</span><code>int(11) DEFAULT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8;</code></li><li><span>在MySQL当中，凡是标识符是可以使用反引号括起来的。</span></li></ul></li><li><p><span>mysql默认使用的存储引擎是InnoDB方式，默认采用的字符集是UTF8。</span></p></li><li><p><span>存储引擎这个名字只有在MySQL中存在。（Oracle中有对应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是“表的存储方式”）</span></p></li></ul><h3 id='常见的存储引擎'><span>常见的存储引擎</span></h3><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang=""><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang=""><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">Engine: MyISAM</span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>  Support: YES</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>  Comment: MyISAM storage engine</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">Transactions: NO</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span> XA: NO</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">  Savepoints: NO</span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 134px;"></div><div class="CodeMirror-gutters" style="display: none; height: 134px;"></div></div></div></pre><ul><li><p><span>MyISAM这种存储引擎不支持事务。</span></p></li><li><p><span>MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。</span></p></li><li><p><span>MyISAM采用三个文件组织一张表：</span></p><ul><li><span>xxx.frm（存储格式的文件）</span></li><li><span>xxx.MYD（存储表中数据的文件）</span></li><li><span>xxx.MYI（存储表中索引的文件）</span></li></ul></li><li><p><span>优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。</span></p></li><li><p><span>缺点：不支持事务。</span></p></li></ul><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang=""><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang=""><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">Engine: InnoDB</span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>  Support: DEFAULT</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>  Comment: Supports transactions, row-level locking, and foreign keys</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">Transactions: YES</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span> XA: YES</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">  Savepoints: YES</span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 157px;"></div><div class="CodeMirror-gutters" style="display: none; height: 157px;"></div></div></div></pre><ul><li><span>优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。</span></li><li><span>表的结构存储在xxx.frm文件中</span></li><li><span>数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。</span></li><li><span>这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。</span></li><li><span>InnoDB支持级联删除和级联更新。</span></li></ul><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang=""><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang=""><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">Engine: MEMORY</span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>  Support: YES</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span>  Comment: Hash based, stored in memory, useful for temporary tables</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">Transactions: NO</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span><span class="cm-tab" role="presentation" cm-text="	">    </span> XA: NO</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">  Savepoints: NO</span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 157px;"></div><div class="CodeMirror-gutters" style="display: none; height: 157px;"></div></div></div></pre><ul><li><span>缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。</span></li><li><span>优点：查询速度最快。</span></li><li><span>以前叫做HEPA引擎。</span></li></ul><h2 id='事务tcltransaction）'><span>事务TCL（Transaction）</span></h2><h3 id='事务概述'><span>事务概述</span></h3><ul><li><p><span>一个事务是一个完整的业务逻辑单元，不可再分。</span></p></li><li><p><span>事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。</span></p></li><li><p><span>和事务相关的语句只有DML语句（delete insert update）。</span></p></li><li><p><span>事务的一些概念</span></p><ul><li><span>事务（Transaction）：一批操作（一组DML）</span></li><li><span>开启事务</span><code>Start Transaction</code></li><li><span>回滚事务</span><code>rollback</code></li><li><span>提交事务</span><code>commit</code></li><li><span>SET AUTOCOMMIT：禁用或启用事务的自动提交模式</span></li></ul></li><li><p><span>rollback或者commit后事务就结束了。</span></p></li></ul><h3 id='事务的特性'><span>事务的特性</span></h3><p><strong><span>事务具有四个特征ACID</span></strong><span>  </span></p><ol start='' ><li><p><span>原子性（Atomicity）</span></p><ul><li><span>整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。</span></li></ul></li><li><p><span>一致性（Consistency）</span></p><ul><li><span>在事务开始之前与结束之后，数据库都保持一致状态。</span></li></ul></li><li><p><span>隔离性(Isolation)</span></p><ul><li><span>一个事务不会影响其他事务的运行。</span></li></ul></li><li><p><span>持久性(Durability)</span></p><ul><li><span>在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。</span></li></ul></li></ol><h3 id='自动提交模式'><span>自动提交模式</span></h3><ul><li><p><span>MySQL是默认自动提交的</span></p></li><li><p><span>自动提交模式用于决定新事务如何及何时启动。 </span></p></li><li><p><span>启用自动提交模式：</span></p><ul><li><span>如果自动提交模式被启用，则单条DML语句将缺省地开始一个新的事务。 </span></li><li><span>如果该语句执行成功，事务将自动提交，并永久地保存该语句的执行结果。 </span></li><li><span>如果语句执行失败，事务将自动回滚，并取消该语句的结果。 </span></li><li><span>在自动提交模式下，仍可使用START TRANSACTION语句来显式地启动事务。这时，一个事务仍可包含多条语句，直到这些语句被统一提交或回滚。 </span></li></ul></li><li><p><span>禁用自动提交模式： </span></p><ul><li><span>如果禁用自动提交，事务可以跨越多条语句。 </span></li><li><span>在这种情况下，事务可以用COMMIT和ROLLBACK语句来显式地提交或回滚。 </span></li></ul></li><li><p><span>自动提交模式可以通过服务器变量AUTOCOMMIT来控制。 </span></p></li><li><p><span>例如：</span></p></li></ul><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="mysql" style="break-inside: unset;"><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang="mysql"><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql<span class="cm-operator">&gt;</span> <span class="cm-keyword">SET</span> <span class="cm-keyword">AUTOCOMMIT</span> <span class="cm-operator">=</span> OFF； </span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql<span class="cm-operator">&gt;</span> <span class="cm-keyword">SET</span> <span class="cm-keyword">AUTOCOMMIT</span> <span class="cm-operator">=</span> <span class="cm-keyword">ON</span>； </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">或</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql<span class="cm-operator">&gt;</span> <span class="cm-keyword">SET</span> <span class="cm-keyword">SESSION</span> <span class="cm-keyword">AUTOCOMMIT</span> <span class="cm-operator">=</span> OFF； </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql<span class="cm-operator">&gt;</span> <span class="cm-keyword">SET</span> <span class="cm-keyword">SESSION</span> <span class="cm-keyword">AUTOCOMMIT</span> <span class="cm-operator">=</span> <span class="cm-keyword">ON</span>； </span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">show</span> <span class="cm-keyword">variables</span> <span class="cm-keyword">like</span> <span class="cm-string">'%auto%'</span><span class="cm-punctuation">;</span> <span class="cm-comment">-- 查看变量状态</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">------------------------------------------------</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql<span class="cm-operator">&gt;</span> <span class="cm-keyword">show</span> <span class="cm-keyword">variables</span> <span class="cm-keyword">like</span> <span class="cm-string">'%auto%'</span><span class="cm-punctuation">;</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+-----------------------------+-------+</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> Variable_name &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class="cm-operator">|</span> <span class="cm-keyword">Value</span> <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+-----------------------------+-------+</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> auto_increment_increment &nbsp; &nbsp;<span class="cm-operator">|</span> <span class="cm-number">1</span> &nbsp; &nbsp; <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> auto_increment_offset &nbsp; &nbsp; &nbsp; <span class="cm-operator">|</span> <span class="cm-number">1</span> &nbsp; &nbsp; <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> <span class="cm-keyword">autocommit</span> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> <span class="cm-keyword">ON</span> &nbsp; &nbsp;<span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> automatic_sp_privileges &nbsp; &nbsp; <span class="cm-operator">|</span> <span class="cm-keyword">ON</span> &nbsp; &nbsp;<span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> innodb_autoextend_increment <span class="cm-operator">|</span> <span class="cm-number">8</span> &nbsp; &nbsp; <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> innodb_autoinc_lock_mode &nbsp; &nbsp;<span class="cm-operator">|</span> <span class="cm-number">1</span> &nbsp; &nbsp; <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">|</span> sql_auto_is_null &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<span class="cm-operator">|</span> OFF &nbsp; <span class="cm-operator">|</span></span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">+-----------------------------+-------+</span></span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 426px;"></div><div class="CodeMirror-gutters" style="display: none; height: 426px;"></div></div></div></pre><h3 id='事务的隔离级别'><span>事务的隔离级别</span></h3><h4 id='隔离级别'><span>隔离级别</span></h4><ul><li><p><span>事务的隔离级别决定了事务之间可见的级别。</span></p></li><li><p><span>当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：</span></p><ul><li><strong><span>脏读取（Dirty Read）</span></strong><span> 一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取。</span></li><li><strong><span>不可重复读（Non-repeatable Read）</span></strong><span> 在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。</span></li><li><strong><span>幻像读（Phantom Read）</span></strong><span> 幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。</span></li></ul></li></ul><h4 id='四个隔离级别'><span>四个隔离级别</span></h4><ul><li><p><span>InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：</span></p><ul><li><strong><span>读未提交（READ UMCOMMITTED）</span></strong><span> 允许一个事务可以看到其他事务未提交的修改。</span></li><li><strong><span>读已提交（READ COMMITTED）</span></strong><span> 允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。</span></li><li><strong><span>可重复读（REPEATABLE READ）</span></strong><span> 确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。 （银行总账）</span>
<span>该隔离级别为InnoDB的缺省设置。</span></li><li><strong><span>串行化（SERIALIZABLE） 【序列化】</span></strong><span> 将一个事务与其他事务完全地隔离。 </span></li></ul></li><li><p><span>mysql数据库默认的隔离级别是：可重复读。</span></p></li><li><p><span>oracle数据库默认的隔离级别是：读已提交。</span></p></li></ul><h4 id='设置隔离级别'><span>设置隔离级别</span></h4><ul><li><p><span>通过修改配置文件设置 </span></p></li><li><p><span>可以在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。 </span></p></li><li><p><span>该选项值可以是： </span></p><ul><li><span>READ-UNCOMMITTED</span></li><li><span>READ-COMMITTED</span></li><li><span>REPEATABLE-READ</span></li><li><span>SERIALIZABLE</span></li></ul></li><li><p><span>例如：[mysqld] transaction-isolation = READ-COMMITTED</span></p></li><li><p><span>通过命令动态设置隔离级别</span></p></li><li><p><span>隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。 </span></p></li><li><p><span>其语法模式为：</span><code>SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL &lt;isolation-level&gt;</code></p><ul><li><p><span>其中的</span><isolation-level><span>可以是：</span></p><ul><li><span>READ UNCOMMITTED</span></li><li><span>READ COMMITTED</span></li><li><span>REPEATABLE READ</span></li><li><span>SERIALIZABLE</span></li></ul></li></ul></li><li><p><span>例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;</span></p></li></ul><h4 id='隔离级别的作用范围'><span>隔离级别的作用范围</span></h4><ul><li><p><span>事务隔离级别的作用范围分为两种： </span></p><ul><li><span>全局级：对所有的会话有效 </span></li><li><span>会话级：只对当前的会话有效 </span></li></ul></li><li><p><span>例如，设置会话级隔离级别为READ COMMITTED ：</span></p><ul><li><code>mysql&gt; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;</code></li><li><code>mysql&gt; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;</code></li></ul></li><li><p><span>设置全局级隔离级别为READ COMMITTED ： </span></p><ul><li><code>mysql&gt; SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;</code></li></ul></li></ul><h4 id='查看隔离级别'><span>查看隔离级别</span></h4><ul><li><p><span>服务器变量tx_isolation（包括会话级和全局级两个变量）中保存着当前的会话隔离级别。 </span></p></li><li><p><span>为了查看当前隔离级别，可访问tx_isolation变量： </span></p><ul><li><p><span>查看会话级的当前隔离级别：</span></p><ul><li><code>mysql&gt; SELECT @@tx_isolation;</code></li><li><code>mysql&gt; SELECT @@session.tx_isolation;</code></li></ul></li><li><p><span>查看全局级的当前隔离级别： </span></p><ul><li><code>mysql&gt; SELECT @@global.tx_isolation;</code></li></ul></li></ul></li></ul><h2 id='索引'><span>索引</span></h2><ul><li><p><span>如果不使用索引会全表扫描。</span></p></li><li><p><span>MYISAM和INNODB都是用B+Tree作为索引结构</span></p></li><li><p><span>怎么创建索引对象</span></p><ul><li><span>创建索引：</span><code>create index 索引名称 on 表名(字段名);</code></li><li><span>查看索引：</span><code>show index from 表名;</code></li><li><span>使用索引：</span><code>explain select语句;</code><span>，不可使用</span><code>select *</code></li><li><span>删除索引：</span><code>drop index 索引名称 on 表名;</code></li></ul></li><li><p><span>什么时候添加索引？</span></p><ul><li><span>表中该字段中的数据量庞大</span></li><li><span>经常被检索，经常出现在where子句中的字段</span></li><li><span>经常被DML操作的字段不建议添加索引</span></li></ul></li><li><p><span>主键和具有unique约束的字段自动会添加索引。根据主键查询效率较高。尽量根据主键检索。</span></p></li><li><p><span>索引的分类</span></p><ul><li><span>单一索引：给单个字段添加索引</span></li><li><span>复合索引: 给多个字段联合起来添加1个索引</span></li><li><span>主键索引：主键上会自动添加索引</span></li><li><span>唯一索引：有unique约束的字段上会自动添加索引  </span></li></ul></li><li><p><span>索引会什么时候失效</span></p><ul><li><span>模糊查询的时候第一个通配符是%。</span></li></ul></li></ul><h2 id='视图'><span>视图</span></h2><h3 id='视图概述'><span>视图概述</span></h3><ul><li><p><span>视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。 </span></p></li><li><p><span>视图有时也被成为“虚拟表”。 </span></p></li><li><p><span>视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。</span></p></li><li><p><span>相对于从基表中直接获取数据，视图有以下好处：</span></p><ul><li><span>访问数据变得简单</span></li><li><span>可被用来对不同用户显示不同的表的内容</span></li></ul></li><li><p><span>用来协助适配表的结构以适应前端现有的应用程序</span></p></li><li><p><span>视图作用：</span></p><ul><li><span>提高检索效率</span></li><li><span>隐藏表的实现细节【面向视图检索】</span></li></ul></li></ul><h3 id='视图操作'><span>视图操作</span></h3><ul><li><span>创建视图：</span><code>create view 视图名 as select语句</code></li><li><span>删除视图：</span><code>drop view 视图名</code></li><li><span>只有DQL语句才能以视图对象的方式创建出来。</span></li><li><span>对视图的增删改查会影响到原表数据。</span></li></ul><h2 id='dba命令'><span>DBA命令</span></h2><h3 id='数据导出'><span>数据导出</span></h3><ul><li><p><span>将数据库当中的数据导出（不用登录）</span></p><ul><li><span>导出整个库：</span><code>mysqldump 数据库名 &gt; &quot;路径&quot; -uroot -p</code></li><li><span>导出单个表：</span><code>mysqldump 数据库名 表名 &gt; &quot;路径&quot; -uroot -p</code></li></ul></li><li><p><span>导入数据</span></p><ul><li><code>create database bjpowernode;</code></li><li><code>use bjpowernode;</code></li><li><code>source D:\bjpowernode.sql</code></li></ul></li></ul><h3 id='远程连接'><span>远程连接</span></h3><p><code>mysql -h 192.168.241.128 -P 3306 -uroot -p</code></p><h3 id='授权dcl）'><span>授权（DCL）</span></h3><h4 id='用户授权'><span>用户授权</span></h4><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang=""><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang=""><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2002px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant option;</span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 45px;"></div><div class="CodeMirror-gutters" style="display: none; height: 45px;"></div></div></div></pre><ul><li><span>all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。</span></li><li><span>on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。如果我要指定将权限应用到test库的user表中，可以这么写：test.user</span></li><li><span>to：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”yangxin”@”192.168.0.%”，表示yangxin这个用户只能在192.168.0IP段登录</span></li><li><span>identified by：指定用户的登录密码</span></li><li><span>with grant option; 表示该用户还可以授权给其他用户</span></li></ul><blockquote><p><span>可以使用GRANT给用户添加权限，权限会自动叠加，不会覆盖之前授予的权限，比如你先给用户添加一个SELECT权限，后来又给用户添加了一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。</span></p></blockquote><h4 id='刷新权限'><span>刷新权限</span></h4><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang=""><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang=""><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; flush privileges;</span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 22px;"></div><div class="CodeMirror-gutters" style="display: none; height: 22px;"></div></div></div></pre><h4 id='查看用户权限'><span>查看用户权限</span></h4><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang=""><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang=""><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2002px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; grant select,create,drop,update,alter on *.* to 'yangxin'@'localhost' identified by 'yangxin0917' with grant option;</span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 67px;"></div><div class="CodeMirror-gutters" style="display: none; height: 67px;"></div></div></div></pre><h4 id='回收权限'><span>回收权限</span></h4><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang=""><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang=""><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2002px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> mysql&gt; revoke create on *.* from 'yangxin@localhost';</span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"> mysql&gt; flush privileges;</span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 67px;"></div><div class="CodeMirror-gutters" style="display: none; height: 67px;"></div></div></div></pre><h4 id='删除用户'><span>删除用户</span></h4><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang=""><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang=""><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; select host,user from user;</span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; drop user 'yangxin'@'localhost';</span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 45px;"></div><div class="CodeMirror-gutters" style="display: none; height: 45px;"></div></div></div></pre><h4 id='用户重命名'><span>用户重命名</span></h4><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang=""><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang=""><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">shell&gt; rename user 'test3'@'%' to 'test1'@'%';</span></pre></div></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 22px;"></div><div class="CodeMirror-gutters" style="display: none; height: 22px;"></div></div></div></pre><h4 id='修改密码'><span>修改密码</span></h4><pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang=""><div class="CodeMirror cm-s-inner CodeMirror-wrap" lang=""><div style="overflow: hidden; position: relative; width: 3px; height: 0px; top: 9.2px; left: 8px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"></textarea></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"></div><div class="CodeMirror-gutter-filler" cm-not-content="true"></div><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 0px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre></div><div class="CodeMirror-measure"></div><div style="position: relative; z-index: 1;"></div><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"></div><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: 0px; width: 0px;"></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; use mysql;</span></pre></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql5.7之前</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; update user set password=password('123456') where user='root';</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql5.7之后</span></pre><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">mysql&gt; update user set authentication_string=password('123456') where user='root';</span></pre></div></div></div></div></div><div style="position: absolute; height: 0px; width: 1px; border-bottom: 0px solid transparent; top: 179px;"></div><div class="CodeMirror-gutters" style="display: none; height: 179px;"></div></div></div></pre><h2 id='数据库的设计三范式'><span>数据库的设计三范式</span></h2><ul><li><p><span>设计表的依据。按照这个三范式设计的表不会出现数据冗余。</span></p></li><li><p><strong><span>第一范式</span></strong><span>：每张表都应该有主键，并且第一个字段原子性不可再分。</span></p><ul><li><span>每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求，主要通常采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利行可能就采用一个字段了。</span></li></ul></li><li><p><strong><span>第二范式</span></strong><span>：建立在第一范式的基础上，要求非主键字段完全依赖主键，不可产生部分依赖。</span></p><ul><li><span>如果一个表是单一主键，那么它就复合第二范式，部分依赖和主键有关系</span></li><li><span>多对多：建立两张表和一张关系表，在关系表上添加外键</span></li></ul></li><li><p><strong><span>第三范式</span></strong><span>：在第二范式的基础上，非主键字段不能传递依赖于主键。</span></p><ul><li><span>一对多：将冗余字段单独拿出来建立表，在多的那张表中添加外键指向一的一方的主键。</span></li></ul></li></ul><h3 id='三范式总结'><span>三范式总结</span></h3><ul><li><span>第一范式：有主键，具有原子性，字段不可分割。</span></li><li><span>第二范式：完全依赖，没有部分依赖。</span></li><li><span>第三范式：没有传递依赖。</span></li><li><span>数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求。</span></li><li><span>多对多：两张表，关系表上加外键。</span></li><li><span>一对多：两张表，多的表上加外键。</span></li><li><span>一对一：主键共享、外键唯一。</span></li></ul><p>&nbsp;</p></div></div>

<script>(function(){var e=document.body.parentElement,t=[],n=null,i=document.body.classList.contains("typora-export-collapse-outline"),r=function(e,t,n){document.addEventListener(e,function(e){if(!e.defaultPrevented)for(var i=e.target;i&&i!=this;i=i.parentNode)if(i.matches(t)){!1===n.call(i,e)&&(e.preventDefault(),e.stopPropagation());break}},!1)};function o(){return e.scrollTop}r("click",".outline-expander",function(e){var t=this.closest(".outline-item-wrapper").classList;return t.contains("outline-item-open")?t.remove("outline-item-open"):t.add("outline-item-open"),d(),!1}),r("click",".outline-item",function(e){var t=this.querySelector(".outline-label");if(location.hash="#"+t.getAttribute("href"),i){var n=this.closest(".outline-item-wrapper").classList;n.contains("outline-item-open")||n.add("outline-item-open"),c(),n.add("outline-item-active")}});var a,s,l=function(){var e=o();n=null;for(var i=0;i<t.length&&t[i][1]-e<60;i++)n=t[i]},c=function(){document.querySelectorAll(".outline-item-active").forEach(e=>e.classList.remove("outline-item-active")),document.querySelectorAll(".outline-item-single.outline-item-open").forEach(e=>e.classList.remove("outline-item-open"))},d=function(){if(n){c();var e=document.querySelector('.outline-label[href="#'+(CSS.escape?CSS.escape(n[0]):n[0])+'"]');if(e)if(i){var t=e.closest(".outline-item-open>ul>.outline-item-wrapper");if(t)t.classList.add("outline-item-active");else{for(var r=(e=e.closest(".outline-item-wrapper")).parentElement.closest(".outline-item-wrapper");r;)r=(e=r).parentElement.closest(".outline-item-wrapper");e.classList.add("outline-item-active")}}else e.closest(".outline-item-wrapper").classList.add("outline-item-active")}};window.addEventListener("scroll",function(e){a&&clearTimeout(a),a=setTimeout(function(){l(),d()},300)});var u=function(){s=setTimeout(function(){!function(){t=[];var e=o();document.querySelector("#write").querySelectorAll("h1, h2, h3, h4, h5, h6").forEach(n=>{var i=n.getAttribute("id");t.push([i,e+n.getBoundingClientRect().y])})}(),l(),d()},300)};window.addEventListener("resize",function(e){s&&clearTimeout(s),u()}),u()})();</script></body>
</html>